'''
Created on 30/set/2009

@author: syn
'''
#ciao a tutti

from sqlalchemy.ext.associationproxy import AssociationProxy
from elixir.ext.perform_ddl import perform_ddl

import datetime
from elixir import Entity, using_options, setup_all, Field, Integer, Unicode, \
                   DateTime, Boolean, CHAR, Float
               
                   
from elixir.options import options_defaults
from elixir.relationships import ManyToOne, OneToMany, ManyToMany

options_defaults['shortnames'] = True
options_defaults['table_options'] = dict(mysql_engine="InnoDB")

# TODO: set better ordering for models

class Subject(Entity):
    using_options(inheritance='multi')
    
    id = Field(Integer, primary_key=True, autoincrement=True)
    users = OneToMany('User', cascade='all')
    addresses = OneToMany('Address', cascade='all')

class Bookshop(Subject):
    using_options(inheritance='multi')
    
    company_name = Field(Unicode(100), required=True)
    vat = Field(Unicode(15), required=True)
    iban = Field(Unicode(27))
    orders = OneToMany('Order', order_by=['-date'])
    
    def __str__(self):
        return self.company_name.strip()

class Author(Subject):
    using_options(inheritance='multi', order_by=['surname','name'])
    
    name = Field(Unicode(100), required=True)
    surname = Field(Unicode(100), required=True)
    books = ManyToMany('Book', tablename='books_authors')
    
    def __str__(self):
        return " ".join([self.name, self.surname]) 

class Admin(Subject):
    using_options(inheritance='multi')
    
    name = Field(Unicode(100), required=True)
    surname = Field(Unicode(100), required=True)
    
class User(Entity):
    using_options(order_by='username')

    id = Field(Integer, primary_key=True, autoincrement=True)
    username = Field(Unicode(100), unique=True, required=True)
    password = Field(CHAR(32), required=True, deferred=True) # TODO: find better way to store password
    email = Field(Unicode(70), required=True)
    registration_date = Field(DateTime, required=True, default=datetime.datetime.now)
    last_modify_date = Field(DateTime, required=True)
    active = Field(Boolean, required=True, default=True)
    subject = ManyToOne('Subject', required=True)

class Address(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    address = Field(Unicode(200), required=True)
    postalcode = Field(Unicode(10))
    city = Field(Unicode(100), required=True)
    province = Field(Unicode(5))
    country = Field(Unicode(50))
    # FIXME: Too specific, MSEnum it's only usable on Mysql... got a problem if we wanna change 
    # to other dbms (e.g. postgres or sqlite). Can use an address_type table and a trigger
    #type = Field(MSEnum("head","registered","other",strict=True), required=True, default="head")
    type = Field(Unicode(20), required=True, default="head")
    subject = ManyToOne('Subject', required=True, ondelete='cascade')

class Book(Entity):
    using_options(order_by='title')
    
    id = Field(Integer, primary_key=True, autoincrement=True)
    title = Field(Unicode(100), required=True)
    description = Field(Unicode(500), deferred=True)
    authors = ManyToMany('Author', tablename='books_authors')
    genres = ManyToMany('Genre', tablename='book_has_genres')
    
    product_books = OneToMany('ProductBooks')
    products = AssociationProxy('product_books', 'product', 
                                creator=lambda product: ProductBooks(product=product))

class Genre(Entity):
    using_options(order_by='name')
    
    id = Field(Integer, primary_key=True, autoincrement=True)
    name = Field(Unicode(20), required=True, unique=True)
    description = Field(Unicode(500), deferred=True)
    books = ManyToMany('Book', tablename='book_has_genres')

class ProductBooks(Entity):
    product = ManyToOne('Product', primary_key=True, ondelete='cascade')
    book = ManyToOne('Book', primary_key=True)
    sequence_num = Field(Integer)

class Product(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    name = Field(Unicode(100), required=True, unique=True)
    description = Field(Unicode(500), deferred=True)
    edition = ManyToOne('Edition', ondelete='set null')
    price = Field(Float(2), required=True)
    quantity = Field(Integer, required=True, default=0)
    _discount = Field(Float(2), colname='discount', synonym='discount')
    
    product_books = OneToMany('ProductBooks', order_by=['sequence_num'], cascade='all')
    books = AssociationProxy('product_books', 'book',
                             creator=lambda book: ProductBooks(book=book))
    
    def _set_discount(self, discount):
        if discount > 0:
            self._discount = discount
        else:
            self._discount = None        
    def _get_discount(self):
        if self._discount == None:
            return 0
        else: 
            return self._discount
    
    discount = property(_get_discount, _set_discount)
    orders = OneToMany('OrderDetail')
    prints = OneToMany('Print', cascade='all')
    
class Edition(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    name = Field(Unicode(100), required=True, unique=True)
    description = Field(Unicode(500), deferred=True)
    products = OneToMany('Product')

class Order(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    bookshop = ManyToOne('Bookshop', required=True)
    date = Field(DateTime, required=True, default=datetime.datetime.now)
    status = Field(Unicode(20), required=True, default='new')
    total_amount = Field(Float(2))
    detail = OneToMany('OrderDetail', cascade='all')
    
class OrderDetail(Entity):
    order = ManyToOne('Order', primary_key=True, ondelete='cascade')
    product = ManyToOne('Product', primary_key=True)
    quantity = Field(Integer, required=True)
    
class Print(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    product = ManyToOne('Product', required=True, ondelete='cascade')
    date = Field(DateTime)
    quantity = Field(Integer, required=True)
    number = Field(Integer) # A trigger in the database calculate this field
    
setup_all()

def create_tables(drop=False):
    from elixir import create_all, drop_all
    if drop:
        drop_all()
    create_all()
